Лабораторна робота №7 з курсу “Організація баз даних і знань” На тему: Групування даних. Вибірка даних Приклад 1. Оператор: SELECT COUNT(*) AS num_prods FROM products WHERE vend_id = 1003; Результат: +-----------+ | num_prods | +-----------+ | 7 | +-----------+ Приклад 2. Оператор: SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id; Результат: +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | 1001 | 3 | | 1002 | 2 | | 1003 | 7 | | 1005 | 2 | +---------+-----------+ Приклад 3. Оператор: SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2; Результат: +---------+--------+ | cust_id | orders | +---------+--------+ | 10001 | 2 | +---------+--------+ Приклад 4. Оператор: SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2; Результат: +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | 1003 | 4 | | 1005 | 2 | +---------+-----------+ Приклад 5. Оператор: SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2; Результат: +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | 1001 | 3 | | 1002 | 2 | | 1003 | 7 | | 1005 | 2 | +---------+-----------+ Приклад 6. Оператор: SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50; Результат: +-----------+------------+ | order_num | ordertotal | +-----------+------------+ | 20005 | 149.87 | | 20006 | 55.00 | | 20007 | 1000.00 | | 20008 | 125.00 | +-----------+------------+ Приклад 7. Оператор: SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal; Результат: +-----------+------------+ | order_num | ordertotal | +-----------+------------+ | 20006 | 55.00 | | 20008 | 125.00 | | 20005 | 149.87 | | 20007 | 1000.00 | +-----------+------------+ Література: SQL Полное руководство – Грофф Ю Н Артеменко MySQL Справочник по язык Справочник по SQL Р.Яргер, Дж.Риз, Т.Кинг MySQL и mSAL. Базы данныз для небольших предприятий и интернета.